// 数据库相关操作
const mysql = require('mysql2/promise')
const config = require('../config/index.js')
const { escape } = require('../utils/serurity.js')

// 创建线程池（连接池）
const pool = mysql.createPool({
    host: config.mysql.host,
    port: config.mysql.port,
    user: config.mysql.user,
    password: config.mysql.password,
    database: config.mysql.database
})

// 执行 sql 语句
const allService = {
    async query(sql, values) {
        try {
            sql = escape(sql);
            // 通过连接池连接MySQL
            const connection = await pool.getConnection();
            // 执行各种增删改查的 sql 语句
            // rows:查询到的结果
            // fields:查询到的字段
            const [rows, fields] = await connection.query(sql, values)
            console.log(rows);

            // 释放连接
            connection.release()
            return rows
        } catch (error) {
            // console.log('查询失败', error);

            return Promise.reject(error)
        }
    }
}

// 登录接口要执行的函数
const userLogin = (username, passowrd) => {
    let _sql = `select * from user where username = '${username}' and password = '${passowrd}'`
    // console.log(_sql);
    return allService.query(_sql)
}

// 注册接口要执行的函数
const register = ({ username, password, nickname, create_time }) => {
    // 防止sql注入
    // username = escape(username);
    // password = escape(password);
    // nickname = escape(nickname);
    let _sql = `insert into user (username, password, nickname, create_time) values ('${username}', '${password}', '${nickname}', '${create_time}')`
    // console.log(_sql);
    return allService.query(_sql)
}

// 查找用户的笔记列表
const findNoteListByType = (id, type) => {
    let _sql = `select * from memory where userId = '${id}' and type = '${type}'`
    // console.log(_sql);
    return allService.query(_sql)
}

// 查找用户的笔记详情
const findNoteById = (id) => {
    let _sql = `select * from memory where id = '${id}'`
    // console.log(_sql);
    return allService.query(_sql)
}

const saveNote = ({ userId, author, title, content, images, type, create_time }) => {
    // 将图片数组转换为JSON字符串存储
    console.log(userId, author, title, content, images, type, create_time);

    const imagesJson = JSON.stringify(images || []);
    let _sql = `insert into memory (userId, author, title, content, images, type, create_time) values ('${userId}', '${author}', '${title}', '${content}', '${imagesJson}', '${type}', '${create_time}')`
    console.log(_sql);
    return allService.query(_sql)
}


module.exports = {
    userLogin,
    register,
    findNoteListByType,
    findNoteById,
    saveNote,
}
